﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using thailandProject.ViewModel;
using System.Web.Mvc;
using iTextSharp;

namespace thailandProject.Models
{
    public class ReportingMethods
    {

        
        // user for data source of dropdown menu
        public static List<string> getActiveProperties()
        {
            ThailandEntities te = new ThailandEntities();
            
           

                var properties =  from property in te.PROPERTies
                                  where property.propIsActive == true
                                  && property.propName != ""
                                  orderby property.propName
                                  select property.propName;
            
            
               var propList = properties.ToList();
               propList.Add("All");
              
            return propList;
        }

        public static List<ApartmentReservation> getSortedReservationList(string sortType, string propertyName, DateTime dateFrom, DateTime dateTo, bool showAllReservations, bool ascen)
        {
            List<ApartmentReservation> result = new List<ApartmentReservation>();
            try
            {
                
                using (ThailandEntities a = new ThailandEntities())
                {
       
                    

                       var reservations = from r in a.RESERVATIONs
                                           join c in a.CLIENTs on r.clientID equals c.clientID
                                           join p in a.PROPERTies on r.propID equals p.propID
                                           join pa in a.PAYMENTs on r.resID equals pa.resID into pay
                                           from payment in pay.DefaultIfEmpty()

                                           where c.clientIsActive == true &&
                                               r.resStartDate >= dateFrom &&
                                               r.resStartDate <= dateTo &&
                                               p.propIsActive == true &&
                                               (propertyName == "All" ? true : p.propName == propertyName) &&
                                               (showAllReservations == true ? true : r.resIsHeld == false)

                                           group payment by new { r.resID, c.clientFirstName, c.clientLastName, p.propName, r.resStartDate, r.resEndDate, r.resQuotedPrice }
                                               into g

                                               select new ApartmentReservation
                                               {
                                                   clientFirstName = g.Key.clientFirstName,
                                                   clientLastName = g.Key.clientLastName,
                                                   propName = g.Key.propName,
                                                   startDate = g.Key.resStartDate,
                                                   endDate = g.Key.resEndDate,
                                                   quotedPrice = g.Key.resQuotedPrice,
                                                   paid = g.Sum(payment => payment == null ? 0 : payment.paymentAmount)
                                               };
                 

                    //NOTE: If we change the sortable columns, then add/remove it here
                       if (ascen)
                       {
                           switch (sortType)
                           {
                               case "clientFirstName": reservations = reservations.OrderBy(r => r.clientFirstName).ThenBy(r => r.startDate); break;
                               case "clientLastName": reservations = reservations.OrderBy(r => r.clientLastName).ThenBy(r => r.startDate); break;
                               case "propName": reservations = reservations.OrderBy(r => r.propName).ThenBy(r => r.startDate); break;
                               case "startDate": reservations = reservations.OrderBy(r => r.startDate); break;
                               case "endDate": reservations = reservations.OrderBy(r => r.endDate); break;
                               case "quotedPrice": reservations = reservations.OrderBy(r => r.quotedPrice).ThenBy(r => r.startDate); break;
                               case "paid": reservations = reservations.OrderBy(r => r.paid).ThenBy(r => r.startDate); break;
                           }
                       }
                       else
                       {
                           switch (sortType)
                           {
                               case "clientFirstName": reservations = reservations.OrderByDescending(r => r.clientFirstName).ThenBy(r => r.startDate); break;
                               case "clientLastName": reservations = reservations.OrderByDescending(r => r.clientLastName).ThenBy(r => r.startDate); break;
                               case "propName": reservations = reservations.OrderByDescending(r => r.propName).ThenBy(r => r.startDate); break;
                               case "startDate": reservations = reservations.OrderByDescending(r => r.startDate); break;
                               case "endDate": reservations = reservations.OrderByDescending(r => r.endDate); break;
                               case "quotedPrice": reservations = reservations.OrderByDescending(r => r.quotedPrice).ThenBy(r => r.startDate); break;
                               case "paid": reservations = reservations.OrderByDescending(r => r.paid).ThenBy(r => r.startDate); break;
                           }
                       }

                    result = reservations.ToList();
                }
                
            }
            catch (Exception ex)
            {
                ErrorLog.logError(ex);
            }

            return result;
        }

        public static List<PickupTasks> getSortedPickupTaskList(DateTime dateFrom, DateTime dateTo)
        {
            List<PickupTasks> result = new List<PickupTasks>();
            try
            {

                using (ThailandEntities a = new ThailandEntities())
                {



                    var pickupTasks = from r in a.RESERVATIONs
                                       join c in a.CLIENTs on r.clientID equals c.clientID
                                       join p in a.PROPERTies on r.propID equals p.propID
                                       join t in a.TASKs on r.resID equals t.resID 
                                       join tt in a.TASKTYPEs on t.taskTypeID equals tt.taskTypeID 
      

                                       where c.clientIsActive == true &&
                                            t.taskDueDate >= dateFrom &&
                                            t.taskDueDate <= dateTo &&
                                            t.taskName.Contains("Pickup") //default is not case sensitive

                                           select new PickupTasks
                                           {
                                               clientFirstName = c.clientFirstName,
                                               clientLastName = c.clientLastName,
                                               propName = p.propName,
                                               taskName = t.taskName,
                                               taskDueDate = t.taskDueDate,
                                               taskDetails = t.taskDetails
                                               
                                           };


 

                    result = pickupTasks.ToList();
                }

            }
            catch (Exception ex)
            {
                ErrorLog.logError(ex);
            }

            return result;
        }
    }
}